

**********************************************************************
**US plants
**
********************************************************************

********************************************************************************
* EIA 923 Forms 
********************************************************************************

drop _all
tempfile cumulator
quietly save `cumulator', emptyok
forval year=2010(1)2022{

	if `year' == 2022 {
		import excel "../Raw/EIA/EIA_923/f923_2022/EIA923_Schedules_2_3_4_5_M_12_2022_Final.xlsx", clear firstrow cellrange(A6)
		destring YEAR, replace
		append using `cumulator', force
		quietly save `cumulator', replace
	}
	else if `year' == 2010 {
		import excel "../Raw/EIA/EIA_923/f923_2010/EIA923 SCHEDULES 2_3_4_5 Final 2010.xls", clear firstrow cellrange(A8)
		rename Year YEAR
		append using `cumulator', force
		quietly save `cumulator', replace
	}
	else if `year' == 2011 {
		import excel "../Raw/EIA/EIA_923/f923_`year'/EIA923_Schedules_2_3_4_5_`year'_Final_Revision.xlsx", clear firstrow cellrange(A6)
		destring YEAR, replace
		append using `cumulator', force
		quietly save `cumulator', replace
	}
	else if `year' == 2013 {
		import excel "../Raw/EIA/EIA_923/f923_`year'/EIA923_Schedules_2_3_4_5_`year'_Final_Revision.xlsx", clear firstrow cellrange(A6)
		destring YEAR, replace
		append using `cumulator', force
		quietly save `cumulator', replace
	}
	else {
		import excel "../Raw/EIA/EIA_923/f923_`year'/EIA923_Schedules_2_3_4_5_M_12_`year'_Final_Revision.xlsx", clear firstrow cellrange(A6)
		destring YEAR, replace
		append using `cumulator', force
		quietly save `cumulator', replace
	}
		
}


//Collapsing Units to a Plant Level
collapse (sum) NetGenerationMegawatthours, by(YEAR PlantName PlantState)
rename (PlantName YEAR) (facility_name year)


//Dropping some non-coal plants
drop if facility_name == "State-Fuel Level Increment" | facility_name == "Consumer Operations LLC"
drop if strpos(facility_name, "Dam") | strpos(facility_name, "Solar")
drop if inlist(facility_name, "Halifax", "High Plains", "Drop 5", "Richland", "Seminole") | ///
          inlist(facility_name, "Bliss", "Dover", "Cedar Creek", "Bear Creek", "South Plant")
drop if facility_name == "Marshall" & PlantState == "AK"
drop if facility_name == "Oak Grove"
drop if facility_name == "Indian River"
drop if facility_name == "Lake Road"
drop if facility_name == "Niles"

*Manually doing the rest, in cases where there are no matches, we put  XXXX.
replace facility_name = "AIR PRODUCTS MANUFACTURING CORP STOCKTON COGEN" if facility_name == "Ingredion Stockton" 
replace facility_name = "Cliffside" if facility_name == "James E. Rogers Energy Complex" 
replace facility_name = "Iatan" if facility_name == "Iatan"
replace facility_name = "John S. Cooper" if facility_name == "Cooper"
replace facility_name = "La Cygne" if facility_name == "La Cygne"
replace facility_name = "NRG Dunkirk Power" if facility_name == "Dunkirk Generating Plant"
replace facility_name = "Powell Avenue Steam Plant" if facility_name == "XXXXXXX"
replace facility_name = "Marshall" if facility_name == "Marshall (NC)"		  
replace facility_name = "Oak Grove" if facility_name == "Oak Grove (TX)"
replace facility_name = "Wygen I" if facility_name == "Wygen 1"
replace facility_name = "Wygen II" if facility_name == "Wygen 2"
replace facility_name = "Wygen III" if facility_name == "Wygen 3"
replace facility_name = "A B Brown Generating Station" if facility_name == "A B Brown"
replace facility_name = "Asheville Steam Electric Plant" if facility_name == "Asheville"
replace facility_name = "BLACK HILLS CORPORATION - BEN FRENCH POWER PLANT" if facility_name == "Ben French"
replace facility_name = "BLACK HILLS ELECTRIC- W.N. CLARK STATION" if facility_name == "Clark (SD)"
replace facility_name = "Bailly Generating Station" if facility_name == "Bailly"
replace facility_name = "Boswell Energy Center" if facility_name == "Clay Boswell"
replace facility_name = "Brandon Shores LLC" if facility_name == "Brandon Shores"
replace facility_name = "Bremo Power Station" if facility_name == "Bremo Bluff"
replace facility_name = "Brunner Island, LLC" if facility_name == "Brunner Island"
replace facility_name = "CONSOL PA COAL CO LLC/BAILEY PREP PLT" if facility_name == "XXXXXX"
replace facility_name = "Cape Fear Steam Electric Plant" if facility_name == "XXXXXX"
replace facility_name = "Chesterfield Power Station" if facility_name == "Chesterfield"
replace facility_name = "Cliffside" if facility_name == "XXXXXX"
replace facility_name = "Clover Power Station" if facility_name == "Clover"
replace facility_name = "Columbia" if facility_name == "Columbia (WI)"
replace facility_name = "Comanche (470)" if facility_name == "Comanche (CO)"
replace facility_name = "Cope Station" if facility_name == "Cope"
replace facility_name = "Coronado Generating Station" if facility_name == "Coronado"
replace facility_name = "Craig" if facility_name == "Craig (CO)"
replace facility_name = "Crystal River Power Plant" if facility_name == "Crystal River"
replace facility_name = "Dugout Canyon Mine" if facility_name == "XXXXXX"
replace facility_name = "E.W. Brown" if facility_name == "E W Brown"
replace facility_name = "Edgewater (4050)" if facility_name == "Edgewater"
replace facility_name = "Elrama" if facility_name == "Elrama Power Plant"
replace facility_name = "F B Culley Generating Station" if facility_name == "F B Culley"
replace facility_name = "FRUIT OF THE LOOM - JAMESTOWN" if facility_name == "XXXXXX"
replace facility_name = "Fort Martin Power Station" if facility_name == "FirstEnergy Fort Martin Power Station"
replace facility_name = "Gerald Gentleman Station" if facility_name == "Gerald Gentleman"
replace facility_name = "H A Wagner LLC" if facility_name == "Herbert A Wagner"
replace facility_name = "H B Robinson / Darlington Electric Power Plant" if facility_name == "H B Robinson"
replace facility_name = "H F Lee Steam Electric Plant" if facility_name == "XXXXXX"
replace facility_name = "Harbor Beach" if facility_name == "XXXXXX"
replace facility_name = "Harrington Station" if facility_name == "Harrington"
replace facility_name = "Harrison Power Station" if facility_name == "FirstEnergy Harrison Power Station"
replace facility_name = "Hibbard Energy Center" if facility_name == "M L Hibbard"
replace facility_name = "Huntley Power" if facility_name == "C R Huntley Generating Station"
replace facility_name = "Hutsonville" if facility_name == "XXXXXX"
replace facility_name = "Iatan Generating Station" if facility_name == "Iatan"
replace facility_name = "Indian River" if facility_name == "Indian River Generating Station"
replace facility_name = "Intermountain Generating Station" if facility_name == "Intermountain Power Project"
replace facility_name = "J E Corette" if facility_name == "J E Corette Plant"
replace facility_name = "John S. Cooper" if facility_name == "XXXXXX"
replace facility_name = "Johns Manville" if facility_name == "XXXXXX"
replace facility_name = "Kincaid Generation, LLC" if facility_name == "Kincaid Generation LLC"
replace facility_name = "L V Sutton Steam Electric Plant" if facility_name == "L V Sutton Combined Cycle"
replace facility_name = "La Cygne Generating Station" if facility_name == "La Cygne"
replace facility_name = "Lake Road Generating Station" if facility_name == "Lake Road Generating Plant"
replace facility_name = "Laramie River" if facility_name == "Laramie River Station"
replace facility_name = "Laskin Energy Center" if facility_name == "Syl Laskin"
replace facility_name = "Longview Power" if facility_name == "Longview Power Plant"
replace facility_name = "Longview Power" if facility_name == "Longview Power LLC"
replace facility_name = "METTIKI COAL, LLC" if facility_name == "XXXXXX"
replace facility_name = "MOBILE ENERGY SERVICES LLC" if facility_name == "Mobile Energy Services LLC"
replace facility_name = "MT POSO COGENERATION COMPANY, LLC" if facility_name == "Mt Poso Cogeneration"
replace facility_name = "Mayo Electric Generating Plant" if facility_name == "Mayo"
replace facility_name = "McClure River Prep Plant" if facility_name == "McClure" //Big 
replace facility_name = "Meredosia" if facility_name == "XXXXXX"
replace facility_name = "Miami Fort Generating Station" if facility_name == "Miami Fort"
replace facility_name = "Michigan City Generating Station" if facility_name == "Michigan City"
replace facility_name = "Mill Creek" if facility_name == "Mill Creek (KY)"
replace facility_name = "Monongalia County Preparation Plant" if facility_name == "XXXXXX"
replace facility_name = "Monroe" if facility_name == "Monroe (MI)"
replace facility_name = "Montour, LLC" if facility_name == "TalenEnergy Montour"
replace facility_name = "Mount Storm Power Station" if facility_name == "Mt Storm"
replace facility_name = "NRG Dunkirk Power" if facility_name == "NRG Dunkirk Power"
replace facility_name = "Nelson Dewey" if facility_name == "Nelson Dewey Generating Station"
replace facility_name = "Phil Sporn" if facility_name == "Philip Sporn"
replace facility_name = "Potomac River" if facility_name == "Potomac Energy Center, LLC"
replace facility_name = "Powell Avenue Steam Plant" if facility_name == "XXXXXX"
replace facility_name = "R M Schahfer Generating Station" if facility_name == "R M Schahfer"
replace facility_name = "Rawhide Energy Station" if facility_name == "Rawhide"
replace facility_name = "River Valley Generating Station" if facility_name == "River Valley"
replace facility_name = "River Valley Generating Station" if facility_name == "AES Shady Point LLC" 
replace facility_name = "Riverbend" if facility_name == "XXXXXX"
replace facility_name = "Riverside (1081)" if facility_name == "Riverside"
replace facility_name = "Roxboro Steam Electric Plant" if facility_name == "Roxboro"
replace facility_name = "SHAW INDUSTRIES GROUP INC PLANT 4" if facility_name == "XXXXXX"
replace facility_name = "Sam Seymour" if facility_name == "Fayette Power Project"
replace facility_name = "Scholz Electric Generating Plant" if facility_name == "Scholz"
replace facility_name = "St. Clair" if facility_name == "St Clair"
replace facility_name = "Tolk Station" if facility_name == "Tolk"
replace facility_name = "Valley (WEPCO)" if facility_name == "XXXXXX"
replace facility_name = "W H Weatherspoon Steam Electric Plant" if facility_name == "W H Weatherspoon"
replace facility_name = "W H Zimmer Generating Station" if facility_name == "W H Zimmer"
replace facility_name = "WESTAR ENERGY, INC." if facility_name == "XXXXXX"
replace facility_name = "WYOMING LIME PRODUCERS" if facility_name == "XXXXXX"
replace facility_name = "Wabash River Gen Station" if facility_name == "Wabash River"
replace facility_name = "Walter C Beckjord Generating Station" if facility_name == "Walter C Beckjord"
replace facility_name = "Yorktown Power Station" if facility_name == "Yorktown"


save "../Intermediate/EIA_923_forms", replace


********************************************************************************
* EPA Emission Data
********************************************************************************
import excel "../Raw/EPA/Emissions_US_2020.xls", sheet("Total Reported Emissions by Sta") firstrow clear

destring LATITUDE,replace
destring LONGITUDE,replace

foreach v of varlist TOTALREPORTEDEMISSIONS2011-TOTALREPORTEDEMISSIONS2020 {
    replace `v' ="" if `v'== "---"
	replace `v' = subinstr(`v', ",", "",.)
	destring `v', replace
  } 
  
* consolidate duplicates  
duplicates tag GHGRPID, gen(dup)
sort dup GHGRPID
collapse (mean) TOTALREPORTEDEMISSIONS2011-TOTALREPORTEDEMISSIONS2020 (first) FACILITY REPORTEDADDRESS-PARENTCOMPANIES, by(GHGRPID) 

* clean name
replace PARENTCOMPANIES = substr(PARENTCOMPANIES, 1, strpos(PARENTCOMPANIES, "(") - 1)
replace PARENTCOMPANIES = strtrim(PARENTCOMPANIES)
replace PARENTCOMPANIES = strupper(PARENTCOMPANIES)
ren PARENTCOMPANIES plant_company

* reshape to plant-year panel
reshape long TOTALREPORTEDEMISSIONS, i(GHGRPID) j(year)

*adjust a few company names for merge
replace plant_company="PORTLAND GENERAL ELECTRIC COMPANY" if plant_company=="PORTLAND GENERAL ELECTRIC CO"
replace plant_company="TRI-STATE GENERATION AND TRANSMISSION ASSOCIATION INC" if plant_company=="TRI-STATE GENERATION & TRANSMISSION ASSOC INC"
replace plant_company="HOOSIER ENERGY RURAL ELECTRIC COOPERATIVE INC" if plant_company=="HOOSIER ENERGY REC INC"
replace plant_company="OTTER TAIL CORPORATION" if plant_company=="OTTER TAIL CORP"
replace plant_company="TALEN ENERGY CORPORATION" if plant_company=="TALEN ENERGY CORP"
replace plant_company="LONGVIEW POWER LLC" if plant_company=="LONGVIEW INTERMEDIATE HOLDINGS C LLC"
replace plant_company="AMERICAN ELECTRIC POWER COMPANY INC" if plant_company=="AMERICAN ELECTRIC POWER"
replace plant_company="SOUTHERN ILLINOIS POWER CORP" if plant_company=="SOUTHERN ILLINOIS POWER COOPERATIVE"
replace plant_company="AES CORPORATION (THE)" if plant_company=="THE AES CORP"
replace plant_company="DUKE ENERGY CORPORATION" if plant_company=="DUKE ENERGY CORP"
replace plant_company="BLACK HILLS CORPORATION" if plant_company=="BLACK HILLS CORP"
replace plant_company="BLACK HILLS CORPORATION" if plant_company=="BLACK HILLS CORP"
replace plant_company="TRANSALTA CORPORATION" if plant_company=="TRANSALTA USA INC"
replace plant_company="SALT RIVER PROJECT" if plant_company=="SALT RIVER PROJECT AGRICULTURAL IMPROVEMENT & POWER DISTRICT"
replace plant_company="ALLIANCE RESOURCE PARTNERS LP" if plant_company=="ALLIANCE HOLDINGS GP LP"
replace plant_company="ALPHA METALLURGICAL RESOURCES INC" if plant_company=="ALPHA METALLURGICAL RESOURCES, LLC"
replace plant_company="PACIFICORP" if plant_company=="BERKSHIRE HATHAWAY INC"
replace plant_company="FORESIGHT ENERGY LP" if plant_company=="MURRAY ENERGY HOLDINGS CO"
replace plant_company="NAVAJO TRANSITIONAL ENERGY CO LLC" if plant_company=="THE NAVAJO NATION TRIBAL GOVERNMENT"
replace plant_company="NAVAJO TRANSITIONAL ENERGY CO LLC" if plant_company=="CONSOL ENERGY INC"

ren (TOTALREPORTEDEMISSIONS FACILITY) (co2_emission facility_name)
keep co2_emission facility_name plant_company year

tempfile plant_emission_US
save `plant_emission_US'

********************************************************************
**********************************************************************
** Euro plants
********************************************************************
********************************************************************

**********************************************************************
**  ENTSO-E via Energy Chart (Average Weekly Data)
**********************************************************************
drop _all
tempfile yearly_cumulator
quietly save `yearly_cumulator', emptyok


foreach country in "Germany" "Poland" "France" "Austria" "Czech_Republic" "Finland" "Greece" "Hungary" "Italy" {
	
	
	
foreach coal_type in "Brown Coal" "Hard Coal" {
	if "`coal_type'" == "Brown Coal" & ("`country'" == "France" | "`country'" == "Austria" | "`country'" == "Finland" | "`country'" == "Italy") {
		continue
	}
	if "`coal_type'" == "Hard Coal" & ("`country'" == "Greece" | "`country'" == "Hungary") {
		continue
	}
forval year= 2015(1)2022 {
	
		if "`coal_type'" == "Brown Coal" {
			import excel "../Raw/Energy_Chart/`country'/`coal_type'/energy-charts_Average_net_electricity_generation_from_fossil_brown_coal___lignite_during_one_week_in_`country'_`year'.xlsx", clear firstrow
		}
		else if "`coal_type'" == "Hard Coal" {
			import excel "../Raw/Energy_Chart/`country'/`coal_type'/energy-charts_Average_net_electricity_generation_from_fossil_hard_coal_during_one_week_in_`country'_`year'.xlsx", clear firstrow
		}
	drop if _n == 1

	*Reshaping Data
	ds Date, not
	destring `r(varlist)', replace
	ds Date, not
	collapse (mean) `r(varlist)'
	ds
	rename (`r(varlist)') hourly_ave=
	gen year = `year'
	reshape long hourly_ave, i(year) j(facility_name) string

	*Collapsing on a plant level
	quietly forvalues i = 1/`=_N' {
		forvalues j = 1/3 { //Repeating a couple times to make sure, plant level
			local lastchar = substr(facility_name[`i'], strlen(facility_name[`i']), 1)
			if regexm("`lastchar'", "^[A-Z]$") {
				replace facility_name = substr(facility_name[`i'], 1, strlen(facility_name[`i']) - 1) in `i'
				}
			if regexm("`lastchar'", "^[0-9]$") {
			replace facility_name = substr(facility_name[`i'], 1, strlen(facility_name[`i']) - 1) in `i'
			}
		}
	}
	collapse (sum) hourly_ave, by (facility_name year)

	*Converting to Yearly MWh
	gen country = "`country'"
	gen yearly_mhw = (8760*hourly_ave)

	append using `yearly_cumulator', force
	quietly save `yearly_cumulator', replace
	}
	}
}

drop if yearly_mhw == 0



*Some Manual Matching
replace facility_name = "Adamow" if facility_name == "Adamów"
replace facility_name = "Agios Dimitrios" if facility_name == "AgiosDimitrios"
replace facility_name = "Altbach/Deizisau" if facility_name == "AltbachDeizisau"
replace facility_name = "Amintaio" if facility_name == "Amyntaio"
replace facility_name = "Belchatow" if facility_name == "Belchatów"
replace facility_name = "Chorzow 2" if facility_name == "Chorzów"
replace facility_name = "Dolna Odra" if facility_name == "DolnaOdra"
replace facility_name = "Duisburg-Walsum" if facility_name == "DuisburgWalsum"
replace facility_name = "Duernrohr" if facility_name == "Dürnrohr"
replace facility_name = "Detmarovice" if facility_name == "Dětmarovice"
replace facility_name = "Fiume Santo" if facility_name == "FiumeSanto"
replace facility_name = "Frechen/Wachtberg" if facility_name == "FrechenWachtberg"
replace facility_name = "Hanasaari B" if facility_name == "Hanasaari"
replace facility_name = "Ibbenbueren" if facility_name == "Ibbenbüren"
replace facility_name = "Karlsruhe" if facility_name == "KarlsruheR"
replace facility_name = "Krakow" if facility_name == "KrakówLeg"
replace facility_name = "La Maxe" if facility_name == "LaMaxe"
replace facility_name = "La Spezia" if facility_name == "LaSpezia"
replace facility_name = "Luenen" if facility_name == "Lünen"
replace facility_name = "Meri-Pori" if facility_name == "MeriPori"
replace facility_name = "Naantali 1" if facility_name == "Naantali"
replace facility_name = "Niederaussem" if facility_name == "Niederaußem"
replace facility_name = "Prunerov" if facility_name == "Prunéřov"
replace facility_name = "Schwarze Pumpe" if facility_name == "SchwarzePumpe"
replace facility_name = "Stalowa Wola" if facility_name == "StalowaWolaPasywna"
replace facility_name = "Sulcis Power Station" if facility_name == "Sulcis"
replace facility_name = "Torrevaldaliga Nord" if facility_name == "TorrevaldaligaNord"
replace facility_name = "Turow" if facility_name == "Turów"
replace facility_name = "Tusimice" if facility_name == "Tušimice"
replace facility_name = "Vaskiluoto 2" if facility_name == "Vaskiluoto"
replace facility_name = "Vitry" if facility_name == "VitrysurSeine"

save "../Intermediate/ENTSO-E", replace



**********************************************************************
** Europe Beyond Coal
**********************************************************************
import excel "../Raw/EuroPlantEmission/2022-10-20-Europe_Beyond_Coal-European_Coal_Database.xlsx", sheet("Plant") cellrange(A1:AH1593) firstrow clear
drop if missing(EBCplantID)
order EBCplantID Plantname Owner
sort Owner


replace Owner="Albioma SA" if Owner=="Albioma"
replace Owner="CEZ AS" if Owner=="CEZ"
replace Owner="Electricite de France SA (EDF Group)" if Owner=="EDF"
replace Owner="EnBW Energie Baden-Württemberg AG" if Owner=="EnBW"
replace Owner="Enea SA" if Owner=="Enea"
replace Owner="Enel SpA" if Owner=="Enel"
replace Owner="Energa SA" if Owner=="Energa"
replace Owner="Energeticky a prumyslovy holding a.s. (EPH)" if Owner=="EPH"
replace Owner="EVN AG" if Owner=="EVN"
replace Owner="Fortum Oyj" if Owner=="Fortum"
replace Owner="PGE SA (Polska Grupa Energetyczna SA)" if Owner=="PGE"
replace Owner="Polskie Górnictwo Naftowe i Gazownictwo SA (PGNiG)" if Owner=="PGNiG"
replace Owner="Public Power Corporation SA (PPC)" if Owner=="PPC"
replace Owner="ContourGlobal plc" if Owner=="ContourGlobal"
replace Owner="RWE AG" if Owner=="RWE"
replace Owner="Sev.en Energy Group" if Owner=="Sev.en Energy AG"
replace Owner="STEAG GmbH" if Owner=="Steag"
replace Owner="TAURON Polska Energia S.A" if Owner=="Tauron"
replace Owner="ZE PAK SA Group (Zespół Elektrowni Pątnów Adamów Konin SA)" if Owner=="ZEPAK"
replace Owner="Elektrocieplownia Bedzin SA" if Owner=="EC Będzin SA"
replace Owner="Helen Ltd" if Owner=="Helen"
replace Owner="EFT Rudnik i Termoelektrana Stanari d.o.o" if Owner=="EFT"
replace Owner="Magyar Villamos Művek Zrt (MVM)" if Owner=="MVM"

reshape long co2_, i(EBCplantID) j(year)  
ren (co2_ Plantname Owner) (co2_emission facility_name plant_company)
keep co2_emission facility_name plant_company year

tempfile plant_emission_EU
save `plant_emission_EU'



use "../Intermediate/analysis_panel_firmyear.dta", clear
keep if Continent=="Europe"
ren borrower_name plant_company
duplicates drop
drop if missing(plant_company)
tempfile firm_year_main
save `firm_year_main'
use `plant_emission_EU', clear
joinby plant_company year using `firm_year_main'


merge 1:1 facility_name year using "../Intermediate/ENTSO-E"
keep facility_name _merge
duplicates drop
sort facility_name

compress
tempfile EU_plant_merged
save `EU_plant_merged'

**********************************************************************
** Australia plants
**
********************************************************************
import excel "../Raw/AusPlantEmission/Greenhouse and energy information by designated generation facility 2020-21.xlsx", sheet("Greenhouse and energy informati") firstrow clear
keep if strpos(Primaryfuel, "Coal")
ren (Reportingentity Facilityname TotalemissionstCO2e ElectricityproductionMWh) (plant_company facility_name co2_emission electric_prod_MWh)
gen year=2020
keep co2_emission facility_name plant_company year electric_prod_MWh
tempfile aus2020
save `aus2020'

import excel "../Raw/AusPlantEmission/Greenhouse and energy information by designated generation facility 2019-20.xlsx", sheet("Greenhouse and energy informati") firstrow clear
keep if strpos(Primaryfuel, "Coal")
ren (Reportingentity Facilityname TotalemissionstCO2e ElectricityproductionMWh) (plant_company facility_name co2_emission electric_prod_MWh)
gen year=2019
keep co2_emission facility_name plant_company year electric_prod_MWh
tempfile aus2019
save `aus2019'

import excel "../Raw/AusPlantEmission/Greenhouse and energy information for designated generation facilities 2018-19.xlsx", sheet("Greenhouse and energy informati") firstrow clear
keep if strpos(PrimaryFuel, "Coal")
ren (ReportingEntity FacilityName TotalEmissionstCO2e ElectricityProductionMWh) (plant_company facility_name co2_emission electric_prod_MWh)
gen year=2018
keep co2_emission facility_name plant_company year electric_prod_MWh
tempfile aus2018
save `aus2018'

import excel "../Raw/AusPlantEmission/Greenhouse and energy information for designated generation facilities 2017-18.xlsx", sheet("Greenhouse and energy informati") firstrow clear
keep if strpos(PrimaryFuel, "Coal")
ren (ReportingEntity FacilityName TotalEmissionstCO2e ElectricityProductionMWh) (plant_company facility_name co2_emission electric_prod_MWh)
gen year=2017
keep co2_emission facility_name plant_company year electric_prod_MWh
tempfile aus2017
save `aus2017'

import excel "../Raw/AusPlantEmission/Greenhouse and energy information for designated generation facilities 2016-17.xlsx", sheet("Greenhouse and energy informati") firstrow clear
keep if strpos(PrimaryFuel, "Coal")
ren (ReportingEntity FacilityName TotalEmissionstCO2e ElectricityProductionMwh) (plant_company facility_name co2_emission electric_prod_MWh)
gen year=2016
keep co2_emission facility_name plant_company year electric_prod_MWh
tempfile aus2016
save `aus2016'

import excel "../Raw/AusPlantEmission/Greenhouse and energy information for designated generation facilities 2015-16.xlsx", sheet("Greenhouse and energy informati") firstrow clear
keep if strpos(PrimaryFuel, "Coal")
ren (Controllingcorporation FacilityName TotalEmissionstCO2e ElectricityProductionMwh) (plant_company facility_name co2_emission electric_prod_MWh)
gen year=2015
keep co2_emission facility_name plant_company year electric_prod_MWh
tempfile aus2015
save `aus2015'

import excel "../Raw/AusPlantEmission/Greenhouse and energy information for designated generation facilities 2014-15.xlsx", sheet("Greenhouse and energy informati") firstrow clear
keep if strpos(PrimaryFuel, "Coal")
ren (ReportingEntity FacilityName TotalEmissionstCO2e ElectricityProductionMwh) (plant_company facility_name co2_emission electric_prod_MWh)
gen year=2014
keep co2_emission facility_name plant_company year electric_prod_MWh
tempfile aus2014
save `aus2014'

import excel "../Raw/AusPlantEmission/Greenhouse and energy information for designated generation facilities 2013-14.xlsx", sheet("Greenhouse and energy informati") firstrow clear
destring GreenhouseGasEmissionsTotalE,replace
ren (ReportingEntity FacilityName GreenhouseGasEmissionsTotalE) (plant_company facility_name co2_emission)
gen year=2013
gen electric_prod_MWh = ElectricityProductionGJ*0.2777777
keep co2_emission facility_name plant_company year electric_prod_MWh
tempfile aus2013
save `aus2013'

import excel "../Raw/AusPlantEmission/Greenhouse and energy information for designated generation facilities 2012-13.xlsx", sheet("Greenhouse and energy informati") firstrow clear
ren (ReportingEntity FacilityName TotalEmissionstCO2e) (plant_company facility_name co2_emission)
gen year=2012
gen electric_prod_MWh = ElectricityProductionGJ*0.2777777
keep co2_emission facility_name plant_company year electric_prod_MWh
tempfile aus2012
save `aus2012'

use `aus2012', clear 
append using `aus2013'
append using `aus2014'
append using `aus2015'
append using `aus2016'
append using `aus2017'
append using `aus2018'
append using `aus2019'
append using `aus2020'


collapse (sum) co2_emission aus_electric_prod_MWh = electric_prod_MWh (first) plant_company, by(facility_name year)
bysort facility_name: gen N=_N
drop if N==1 
drop if N==2 & year==2012
bysort facility_name: gen NN=_N
drop if NN==1
drop N NN




replace plant_company="AGL Energy Ltd" if plant_company=="AGL ENERGY LIMITED"
replace plant_company="BHP Group Ltd" if plant_company=="BHP BILLITON IRON ORE PTY. LTD."
replace plant_company="BHP Group Ltd" if plant_company=="BHP Billiton Limited"
replace plant_company="BHP Group Ltd" if plant_company=="BHP GROUP LIMITED"
replace plant_company="CS Energy Ltd" if plant_company=="C S ENERGY LIMITED"
replace plant_company="Origin Energy Ltd" if plant_company=="ORIGIN ENERGY LIMITED"
replace plant_company="Stanwell Corporation Ltd" if plant_company=="Stanwell Corporation Limited"
replace plant_company="Stanwell Corporation Ltd" if plant_company=="STANWELL CORPORATION LIMITED"
replace plant_company="Sunset Power International Pty Ltd (Delta Electricity)" if plant_company=="SUNSET POWER INTERNATIONAL PTY LTD"
replace plant_company="Sunset Power International Pty Ltd (Delta Electricity)" if plant_company=="DELTA ELECTRICITY"
replace plant_company="Sunset Power International Pty Ltd (Delta Electricity)" if plant_company=="DELTA ELECTRICITY PTY LTD"
replace plant_company="Bluewaters Power 1 Pty Ltd" if plant_company=="BLUEWATERS POWER 1 PTY LTD"

tempfile plant_emission_AUS
save `plant_emission_AUS'


**********************************************************************
* Merge with main dataset
**********************************************************************
  
**clean dataset to merge with
use "../Intermediate/analysis_panel_firmyear.dta", clear
keep if Country=="USA"
ren Company_orbis plant_company
replace plant_company = subinstr(plant_company, ".", "",.) 
replace plant_company = subinstr(plant_company, ",", "",.) 
replace plant_company = strtrim(plant_company)
duplicates drop
drop if missing(plant_company)
tempfile firm_year_main
save `firm_year_main'

use `plant_emission_US', clear
joinby plant_company year using `firm_year_main'
drop if facility_name == "Monongalia County Preparation Plant" //dropping this plant to make unique at plant year level
merge 1:1 facility_name year using "../Intermediate/EIA_923_forms", nogen keep(1 3)
compress
tempfile US_plant_merged
save `US_plant_merged'

** EU and AUS company are based on borrower name
use "../Intermediate/analysis_panel_firmyear.dta", clear
keep if Continent=="Europe"
ren borrower_name plant_company
duplicates drop
drop if missing(plant_company)
tempfile firm_year_main
save `firm_year_main'
use `plant_emission_EU', clear
joinby plant_company year using `firm_year_main'

merge 1:1 facility_name year using "../Intermediate/ENTSO-E", nogen keep(1 3)
compress
tempfile EU_plant_merged
save `EU_plant_merged'

use "../Intermediate/analysis_panel_firmyear.dta", clear
keep if Country=="Australia"
ren borrower_name plant_company
duplicates drop
drop if missing(plant_company)
tempfile firm_year_main
save `firm_year_main'
use `plant_emission_AUS', clear
joinby plant_company year using `firm_year_main'
compress
tempfile AUS_plant_merged
save `AUS_plant_merged'


use `US_plant_merged', clear
append using `EU_plant_merged'
append using `AUS_plant_merged'


gen electric_prod_MWh = aus_electric_prod_MWh
replace electric_prod_MWh = NetGenerationMegawatthours if Country == "USA"
replace electric_prod_MWh = yearly_mhw if missing(electric_prod_MWh)


save ../Intermediate/plant_emission_data, replace



**********************************************************************
** PREPARE FOR REGRESSIONS
********************************************************************
use ../Intermediate/plant_emission_data, clear 


bys facility_name: gen cx = _n == 1
sum ss_pre_all_debt_ban_intnstyn1_mx if cx == 1, de
gen ban_intensity_max_sd = ss_pre_all_debt_ban_intnstyn1_mx/`r(sd)'

replace electric_prod_MWh = 0 if electric_prod_MWh < 0
gen co2_intensity = co2_emission/electric_prod_MWh
winsor co2_intensity, p(.01) gen(co2_intensity_win)


egen newid = group(facility_name borrower_id)
tsset newid year

replace co2_emission=0 if missing(co2_emission)
gen active=(co2_emission!=0)


gen co2_emission_2014 = co2_emission if year == 2014
bys newid (co2_emission_2014): replace co2_emission_2014 = co2_emission_2014[1]


gen co2_scaled = co2_emission/co2_emission_2014
replace co2_scaled = . if co2_scaled > 4.2


gen treatment_2015 = (year>=2015)*ban_intensity_max_sd
label var treatment_2015 "$\text{Bank Exit Exposure (Max)}_{f}$ $\times$ $\mathds{1}\{Year \geq 2015\}$ "


gen tag=1 if year==2014 & co2_emission==0
bys facility_name: egen toto=max(tag)
keep if toto == .

save "../Intermediate/Coal_Plant_Emissions", replace


